package org.jctc.els.common.utils;



import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import com.sun.org.apache.xerces.internal.impl.xpath.regex.ParseException;

public class PoiUtil {
	//	   private static Logger logger  = Logger.getLogger(PoiUtil.class);
	private final static String xls = "xls";
	private final static String xlsx = "xlsx";

	private static final String EMPTY = "";
	private static final String POINT = ".";

	/**
	 * 读取excel 接口 先验证文件名称 并根据文件尾缀选择调用方法
	 * @param file
	 * @param sheetNumber  -- 页签数
	 * @param cellFirstNumber --从第几个单元格开始
	 * @param rowFirstNumber -- 从第几行开始
	 * @param rowLastNumber  -- 第几列结束
	 * @return
	 * @throws Exception
	 */
	public static ArrayList<ArrayList<Object>> readExcel(MultipartFile file,int sheetNumber,int cellFirstNumber, int rowFirstNumber,int rowLastNumber)throws Exception{
		//获取文件名
		String fileName = file.getOriginalFilename();
		//转流
		InputStream fileInput = file.getInputStream();
		//验证文件
		if (file == null || PoiUtil.EMPTY.equals(fileName)) {
			return null;
		} else {
			//验证文件尾缀
			String postfix = getFileSyllable(fileName);
			if (!PoiUtil.EMPTY.equals(postfix)) {
				if (PoiUtil.xls.equals(postfix)) {
					return readXls(fileInput,sheetNumber,cellFirstNumber,rowFirstNumber,rowLastNumber);
				} else if (PoiUtil.xlsx.equals(postfix)) {
					return readXlsx(fileInput,sheetNumber ,cellFirstNumber,rowFirstNumber,rowLastNumber);
				}
			} else {
				//不是excel输出文件名称 抛出异常
				System.out.println(fileName);
				throw new Exception("该文件不是Excel文件");
			}
		}

		return null;
	}



	/**
	 * 验证文件尾缀
	 * @param fileName  文件名
	 * @return
	 */
	private   static String getFileSyllable(String fileName){
		if (fileName == null || PoiUtil.EMPTY.equals(fileName.trim())) {
			return PoiUtil.EMPTY;
		}
		if (fileName.contains(PoiUtil.POINT)) {
			return fileName.substring(fileName.lastIndexOf(PoiUtil.POINT) + 1, fileName.length());
		}
		return PoiUtil.EMPTY;
	}
	/**
	 * 读取 excel 2003 xls文件
	 * @param is InputStream
	 * @return  ArrayList<ArrayList<Object>>
	 */
	private static ArrayList<ArrayList<Object>> readXls(InputStream is,int sheetNumber,int cellFirstNumber,int rowFirstNumber,int rowLastNumber) {
		try {
			ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
			ArrayList<Object> colList;
			HSSFWorkbook wb = new HSSFWorkbook(is);
			//HSSFSheet sheet = wb.getSheetAt(0);  读第一个页签
			HSSFSheet sheet = wb.getSheetAt(sheetNumber);
			HSSFRow row;
			HSSFCell cell;
			Object value = null;
			//sheet.getFirstRowNum() + 1  从第二行开始
			for (int i = rowFirstNumber, rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
				row = sheet.getRow(i);
				colList = new ArrayList<Object>();
				if (row == null) {
					if (i != sheet.getPhysicalNumberOfRows()) {// 判断是否是最后一行
						rowList.add(colList);
					}
					return rowList;
				} else {
					rowCount++;
				}
				//j <= row.getLastCellNum()
				for (int j = cellFirstNumber; j <= rowLastNumber; j++) {
					cell = row.getCell(j);
					if (cell == null || cell.getCellType() == CellType.BLANK) {
						if (j != row.getLastCellNum()) {
							colList.add("");
						}
						continue;
					}
					if (null != cell) {
						switch (cell.getCellType()) {
							case NUMERIC:
								if (HSSFDateUtil.isCellDateFormatted(cell)) {
									SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
									value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
									break;
								} else {
									Double d = cell.getNumericCellValue();
									DecimalFormat df = new DecimalFormat("#.##");
									value = df.format(d);
								}
								break;
							case STRING:

								value = cell.getStringCellValue();
								break;
							case BOOLEAN:
								value = cell.getBooleanCellValue() + "";
								break;
							case FORMULA:
								Double a =(Double)cell.getNumericCellValue();
								value = a+"";
								break;
							case BLANK:
								value = "";
								break;
							case ERROR:
								value = "非法字符";
								break;
							default:
								value = "未知类型";
								break;
						}

					}
					colList.add(value);
				}
				rowList.add(colList);
			}
			if (is != null) {
				is.close();
			}
			return rowList;
		} catch (Exception e) {
			return null;
		}
	}
	/**
	 * 读取 excel 2007 2010  xlsx文件
	 * @param is InputStream
	 * @return ArrayList<ArrayList<Object>>
	 */
	private static ArrayList<ArrayList<Object>> readXlsx(InputStream is,int sheetNumber,int cellFirstNumber, int rowFirstNumber,int rowLstNumber) {
		try {
			ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
			ArrayList<Object> colList;
			XSSFWorkbook wb = new XSSFWorkbook(is);
			//读取第一个页签  	XSSFSheet sheet = wb.getSheetAt(0);
			XSSFSheet sheet = wb.getSheetAt(sheetNumber);
			XSSFRow row;
			XSSFCell cell;
			Object value = null;
			//sheet.getFirstRowNum() + 1 页签的第二行开始
			for (int i = rowFirstNumber, rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
				row = sheet.getRow(i);
				colList = new ArrayList<Object>();
				if (row == null) {
					if (i != sheet.getPhysicalNumberOfRows()) {
						rowList.add(colList);
					}
					return rowList;
				} else {
					rowCount++;
				}
				//row.getLastCellNum()
				for (int j = cellFirstNumber; j <= rowLstNumber; j++) {
					cell = row.getCell(j);
					if (cell == null || cell.getCellType() == CellType.BLANK) {
						if (j != row.getLastCellNum()) {
							colList.add("");
						}
						continue;
					}

					if (null != cell) {
						switch (cell.getCellType()) {
							case NUMERIC:
								if (HSSFDateUtil.isCellDateFormatted(cell)) {
									SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
									value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
									break;
								} else {
									Double d = cell.getNumericCellValue();
									DecimalFormat df = new DecimalFormat("#.##");
									value = df.format(d);
								}
								break;

							case STRING:
								value = cell.getStringCellValue();
								break;

							case BOOLEAN:
								value = cell.getBooleanCellValue() + "";
								break;

							case FORMULA:
								Double a =(Double)cell.getNumericCellValue();
								value = a+"";
								break;

							case BLANK:
								value = "";
								break;

							case ERROR:
								value = "非法字符";
								break;

							default:
								value = "未知类型";
								break;
						}

					}
					colList.add(value);
				}
				rowList.add(colList);
			}
			if (is != null) {
				is.close();
			}
			return rowList;
		} catch (Exception e) {
			System.out.println("exception");
			return null;
		}
	}
	//-------------------------------------导出到excel----------------------------------
	/**
	 * 导出Excel表开始
	 * @param sheetName 工作簿名称
	 * @param clazz  数据源model类型
	 * @param objs   excel标题列以及对应model字段名
	 * @param map  标题列行数以及cell字体样式
	 */
	public static XSSFWorkbook createExcelFile(Class clazz, List objs, Map<Integer, List<ExcelBean>> map, String sheetName) throws
			IllegalArgumentException,IllegalAccessException,InvocationTargetException,
			ClassNotFoundException, IntrospectionException, ParseException {
		// 创建新的Excel工作簿
		XSSFWorkbook workbook = new XSSFWorkbook();
		// 在Excel工作簿中建一工作表，其名为缺省值, 也可以指定Sheet名称
		XSSFSheet sheet = workbook.createSheet(sheetName);

		// 以下为excel的字体样式以及excel的标题与内容的创建，下面会具体分析;
		createFont(workbook); //字体样式
		createTableHeader(sheet, map); //创建标题（头）
		createTableRows(sheet, map, objs, clazz); //创建内容
		return workbook;
	}
	/**
	 * 导出Excel表开始
	 */
	public static XSSFWorkbook createExcelFile1(Class clazz1,Class clazz2, List objs1,List cbjs2, Map<Integer, List<ExcelBean>> map1, Map<Integer, List<ExcelBean>> map2, String sheetName1, String sheetName2) throws
			IllegalArgumentException,IllegalAccessException,InvocationTargetException,
			ClassNotFoundException, IntrospectionException, ParseException {
		// 创建新的Excel工作簿
		XSSFWorkbook workbook = new XSSFWorkbook();
		// 在Excel工作簿中建一工作表，其名为缺省值, 也可以指定Sheet名称
		XSSFSheet sheet1 = workbook.createSheet(sheetName1);
		XSSFSheet sheet2 = workbook.createSheet(sheetName2);
		// 以下为excel的字体样式以及excel的标题与内容的创建，下面会具体分析;
		createFont(workbook); //字体样式
		createTableHeader(sheet1, map1); //创建标题（头）
		createTableHeader(sheet2, map2); //创建标题（头）
		createTableRows(sheet1, map1, objs1, clazz1); //创建内容
		createTableRows(sheet2, map2, cbjs2, clazz2); //创建内容
		return workbook;
	}
	private static XSSFCellStyle fontStyle;
	private static XSSFCellStyle fontStyle2;
	public static void createFont(XSSFWorkbook workbook) {
		// 表头
		fontStyle = workbook.createCellStyle();
		XSSFFont font1 = workbook.createFont();
//	            font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
		font1.setFontName("宋体");
		font1.setFontHeightInPoints((short) 14);// 设置字体大小
		//fontStyle.setFont(font1);
//	            fontStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
//	            fontStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
//	            fontStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
//	            fontStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
		// fontStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
		// 内容
		fontStyle2=workbook.createCellStyle();
		XSSFFont font2 = workbook.createFont();
		font2.setFontName("宋体");
		font2.setFontHeightInPoints((short) 10);// 设置字体大小
		fontStyle2.setFont(font2);
//	            fontStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
//	            fontStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
//	            fontStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
//	            fontStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
//	            fontStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
	}
	/**
	 * 根据ExcelMapping 生成列头（多行列头）
	 *
	 * @param sheet 工作簿
	 * @param map 每行每个单元格对应的列头信息
	 */
	public static final void createTableHeader(XSSFSheet sheet, Map<Integer, List<ExcelBean>> map) {
		int startIndex=0;//cell起始位置
		int endIndex=0;//cell终止位置
		for (Map.Entry<Integer, List<ExcelBean>> entry : map.entrySet()) {
			XSSFRow row = sheet.createRow(entry.getKey());
			List<ExcelBean> excels = entry.getValue();
			for (int x = 0; x < excels.size(); x++) {
				//合并单元格
				if(excels.get(x).getCols()>1){
					if(x==0){
						endIndex+=excels.get(x).getCols()-1;
						CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);
						sheet.addMergedRegion(range);
						startIndex+=excels.get(x).getCols();
					}else{
						endIndex+=excels.get(x).getCols();
						CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);
						sheet.addMergedRegion(range);
						startIndex+=excels.get(x).getCols();
					}
					XSSFCell cell = row.createCell(startIndex-excels.get(x).getCols());
					cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容
					if (excels.get(x).getCellStyle() != null) {
						cell.setCellStyle(excels.get(x).getCellStyle());// 设置格式
					}
					cell.setCellStyle(fontStyle);
				}else{
					XSSFCell cell = row.createCell(x);
					cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容
					if (excels.get(x).getCellStyle() != null) {
						cell.setCellStyle(excels.get(x).getCellStyle());// 设置格式
					}
					cell.setCellStyle(fontStyle);
				}
			}
		}
	}
	public static void createTableRows(XSSFSheet sheet, Map<Integer, List<ExcelBean>> map, List objs, Class clazz)
			throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, IntrospectionException,
			ClassNotFoundException, ParseException {
		int rowindex = map.size();
		int maxKey = 0;
		List<ExcelBean> ems = new ArrayList<ExcelBean>();
		for (Map.Entry<Integer, List<ExcelBean>> entry : map.entrySet()) {
			if (entry.getKey() > maxKey) {
				maxKey = entry.getKey();
			}
		}
		ems = map.get(maxKey);
		List<Integer> widths = new ArrayList<Integer>(ems.size());
		for (Object obj : objs) {
			XSSFRow row = sheet.createRow(rowindex);
			for (int i = 0; i < ems.size(); i++) {
				ExcelBean em = (ExcelBean) ems.get(i);
				// 获得get方法
				PropertyDescriptor pd = new PropertyDescriptor(em.getPropertyName(), clazz);
				Method getMethod = pd.getReadMethod();
				Object rtn = getMethod.invoke(obj);
				String value = "";
				// 如果是日期类型进行转换
				if (rtn != null) {
					if (rtn instanceof Date) {
						value = DateFormatUtils.format((Date)rtn, "yyyy-MM-dd");
					} else if(rtn instanceof BigDecimal){
						NumberFormat nf = new DecimalFormat("#,##0.00");
						value=nf.format((BigDecimal)rtn).toString();
					} else if((rtn instanceof Integer) && (Integer.valueOf(rtn.toString())<0 )){
						value="--";
					}else {
						value = rtn.toString();
					}
				}
				XSSFCell cell = row.createCell(i);
				cell.setCellValue(value);
				cell.setCellType(CellType.STRING);
				cell.setCellStyle(fontStyle2);
				// 获得最大列宽
				int width = value.getBytes().length * 300;
				// 还未设置，设置当前
				if (widths.size() <= i) {
					widths.add(width);
					continue;
				}
				// 比原来大，更新数据
				if (width > widths.get(i)) {
					widths.set(i, width);
				}
			}
			rowindex++;
		}
		// 设置列宽
		for (int index = 0; index < widths.size(); index++) {
			Integer width = widths.get(index);
			width = width < 2500 ? 2500 : width + 300;
			width = width > 10000 ? 10000 + 300 : width + 300;
			sheet.setColumnWidth(index, width);
		}

	}

	/**
	 * 下载模板信息
	 * 适用于windows和linux
	 * @param response
	 * @param request
	 * @param templeteName
	 * @throws IOException
	 */
	public static void downloadTemplate(HttpServletResponse response,HttpServletRequest request,String templeteName) throws IOException {
		OutputStream outp = null;
		FileInputStream in = null;
		try {
			String fileName = templeteName; //要下载的模板文件
			if(templeteName!=null){
				if(!templeteName.endsWith(".xls")){
					if(templeteName.endsWith(".xlsx")){
						;
					}else{
						fileName = templeteName + ".xls";
					}

				}
			}
			String ctxPath = request.getSession().getServletContext().getRealPath(File.separator) + File.separator + "upload" + File.separator;
			String filedownload = ctxPath + fileName;
			fileName = URLEncoder.encode(fileName, "UTF-8");
			// 要下载的模板所在的绝对路径
			response.reset();
			response.addHeader("Content-Disposition", "attachment; filename="+fileName);
			response.setContentType("application/octet-stream;charset=UTF-8");
			outp = response.getOutputStream();
			in = new FileInputStream(filedownload);
			byte[] b = new byte[1024];
			int i = 0;
			while ((i = in.read(b)) > 0) {
				outp.write(b, 0, i);
			}
			outp.flush();
		} catch (Exception e) {
			System.out.println("Error!");
			e.printStackTrace();
		} finally {
			if (in != null) {
				in.close();
				in = null;
			}
			if (outp != null) {
				outp.close();
				outp = null;
			}
		}
	}
	public static void downloadTemplate2(HttpServletResponse response,HttpServletRequest request,String templeteName) throws IOException {
		OutputStream outp = null;
		FileInputStream in = null;
		try {
			String fileName = templeteName; //要下载的模板文件
			if(templeteName!=null){
				if(!templeteName.endsWith(".zip")){
					if(templeteName.endsWith(".rar")){
						;
					}else{
						fileName = templeteName + ".zip";
					}

				}
			}
			String ctxPath = request.getSession().getServletContext().getRealPath(File.separator) + File.separator + "photo" + File.separator;
			String filedownload = ctxPath + fileName;
			fileName = URLEncoder.encode(fileName, "UTF-8");
			// 要下载的模板所在的绝对路径
			response.reset();
			response.addHeader("Content-Disposition", "attachment; filename="+fileName);
			response.setContentType("application/octet-stream;charset=UTF-8");
			outp = response.getOutputStream();
			in = new FileInputStream(filedownload);
			byte[] b = new byte[1024];
			int i = 0;
			while ((i = in.read(b)) > 0) {
				outp.write(b, 0, i);
			}
			outp.flush();
		} catch (Exception e) {
			System.out.println("Error!");
			e.printStackTrace();
		} finally {
			if (in != null) {
				in.close();
				in = null;
			}
			if (outp != null) {
				outp.close();
				outp = null;
			}
		}
	}
}
